package com.DeliveryProgram.www.dao;

import com.DeliveryProgram.www.po.*;
import com.DeliveryProgram.www.util.JDBCutil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**用户类信息的读取*/
public class UserLoad {
    private static Connection conn=null;
    private static Statement stmt=null;
    private static ResultSet rs=null;
    private static String sql=null;

    /**用户获取数据库中基础信息*/
    public static void getUser(User user){
        try {
            conn= JDBCutil.getConnection();
            sql="SELECT *FROM USER WHERE NAME='"+user.getName()+"'";
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            while(rs.next()){
                user.setUid(rs.getInt(1));
                user.setMoney(rs.getInt(3));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(rs,stmt,conn);
        }

    }

    /**获取用户拥有的折扣*/
    public static void getCoupons(User user){
        try {
            conn=JDBCutil.getConnection();
            sql="SELECT business.`name`,coupons.`id`,coupons.`discount` FROM USER,business,coupons,hascoupons  WHERE  "+user.getUid()+"=hascoupons.`user` AND hascoupons.`coupons`=coupons.`id` AND coupons.`business`=business.`bid`";
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            while(rs.next()){
                user.setCoupons(new Coupons(rs.getInt(2),rs.getDouble(3),rs.getNString(1)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(rs,stmt,conn);
        }
    }

    /**获取用户的地址*/
    public static void getAdress(User user){
        try {
            conn=JDBCutil.getConnection();
            sql= "SELECT address.`id`,address.`ad`,address.`ph` FROM address WHERE address.`user`="+user.getUid();
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
           while(rs.next()) {
               user.setAddresses(new Address(rs.getInt(1), rs.getNString(2), rs.getNString(3)));
           }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(rs,stmt,conn);
        }
    }

    /**获取用户的订单*/
    public static void getOrders(User user){
        try {
            conn=JDBCutil.getConnection();
            sql="SELECT business.`shopname`, orders.`id`, orders.`date`,orders.`isComplete`,orders.`money`,address.`ad`,address.`ph`,address.`id` FROM orders,address,business WHERE orders.`address`=address.`id` AND orders.`business`=business.`bid`AND orders.`user`="+user.getUid();
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            while(rs.next()){
                user.setOrders(new Order(rs.getInt(2),rs.getInt(4),new Address(rs.getInt(8),rs.getNString(6),rs.getNString(7)),rs.getDate(3),rs.getInt(5),rs.getNString(1)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(rs,stmt,conn);
        }
    }

    /**获取用户的评论*/
    public static void getComment(User user){
        try {
            conn=JDBCutil.getConnection();
            sql="SELECT comment.`id`,comment.`comment`,comment.`date`,business.`shopname` FROM business,COMMENT,usercomment WHERE "+user.getUid()+"=usercomment.`id` AND usercomment.`id`=comment.`id` AND usercomment.`business`=business.`bid`";
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            while(rs.next()){
                user.setComments(new Comment(rs.getInt(1),user.getName(),rs.getNString(4),rs.getNString(2),rs.getDate(3)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(rs,stmt,conn);
        }
    }

    /**向数据库中获取新地址的id*/
    public static void getAddrNewId(User us){
        try {
            conn= JDBCutil.getConnection();
            sql="SELECT id FROM address WHERE USER ="+us.getUid()+" AND ad='"+us.getAddresses().get(us.getAddresses().size()-1).getAddr()+"' AND ph='"+us.getAddresses().get(us.getAddresses().size()-1).getPhone()+"' ";
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            rs.next();
            us.getAddresses().get(us.getAddresses().size()-1).setId(rs.getInt(1));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(rs,stmt,conn);
        }
    }

}
